In my current project we are storing big volume of data in relational database. One of the recent key requirements is to enrich application by adding some advanced search capabilities.

In the Project, performance is one of the important factors due to very large tables (10+ milions of records) with parent-children relations (for example: multi-level parent-child relationship, where I am looking for all parents with specific children). The search engine should also be able to check these references for hits.

I have found some potential engines on stack overflow, however it looks like that all of them are dedicated rather for text search than relational db and hosted on linux os:

• lucene
• Solr
• Sphinx

As I understand some of them use documents as a source of searching, but is it possible or efficient to create programmaticaly documents based on my relational data? As I am not familiar with all of their features/capabilities can anyone please make some recommendations or propose some different solution?

To summarize my requirements:

• framework/engine to search relational database including decendants.
• support for Microsoft SQL Server
• can be used in .NET applications
• preferably hosted on Windows systems

Does any of mentioned above are able to solve my problem? do you know any better solution?